Amazon Redshift の新機能「Auto Vacuum & Auto Sort」の徹底検証 #reinvent

AWS re:Invent 2019

#ビッグデータ

#Amazon Redshift

#AWS

石川覚

2019.12.12

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

先週、AWS re:Invent 2019で発表のあった「Auto Vacuum & Auto Sort」は、機械学習を使用してクエリのパターンを分析した結果に基づき、VACUUMを自動実行する機能です。なんかいい感じで暇な時に自動でVACUUMしてくれる、というニュアンスが伝わってくるのですが、本当のところどうかという点が気になります。そこで、この「機械学習を使用してクエリのパターンを分析」とは何かという点に着目して、実際の動作を確認してみました。

Auto Vacuum & Auto Sort とは

これまでのRedshiftのAuto Vacuum Delete は論理削除した領域の開放のみなので、ソートキーを設定したテーブルは別途VACUUMを実行が必要でした。「Auto Vacuum & Auto Sort」は、Auto Vacuum でデータのソートも自動実行されるようになりました。

何を契機に自動実行するのか？

Redshift は、スキャンクエリを追跡し、ソートするメリットのあるテーブルのセクションを判断し、システムの負荷に応じて、自動でソートを開始します。この機能は、SVV_TABLE_INFOのvacuum_sort_benefitの値が高いテーブルが自動的にVACUUM SORTするという仕様です。

参考： Automatic Table Sort

そこで、実際に毎日頻繁に更新しているクラスタのvacuum_sort_benefitを確認してみました。ところが、vacuum_sort_benefitの値が設定されているテーブルは7つほどしかありません。vacuum_sort_benefitが設定されていないとAuto Vacuum Sortは実行されません。これは困った、、、

sampledb=# SELECT "schema", "table", diststyle, unsorted, vacuum_sort_benefit
sampledb-# FROM SVV_TABLE_INFO
sampledb-# WHERE unsorted IS NOT NULL
sampledb-# ORDER BY vacuum_sort_benefit desc, unsorted desc;
schema | table | diststyle | unsorted | vacuum_sort_benefit
--------+-------+-----------------+----------+---------------------
****** | ***** | EVEN | 1.13 | 27.00
****** | ***** | KEY(fooooooooo) | 4.44 | 10.00
****** | ***** | KEY(bazzzz) | 0.40 | 7.00
****** | ***** | KEY(bazzzz) | 3.54 | 5.00
****** | ***** | KEY(fooooooooo) | 4.14 | 4.00
****** | ***** | EVEN | 1.93 | 3.00
****** | ***** | KEY(fooooooooo) | 3.02 | 2.00
****** | ***** | EVEN | 100.00 | 0.00
****** | ***** | EVEN | 100.00 | 0.00
****** | ***** | ALL | 100.00 | 0.00
****** | ***** | EVEN | 100.00 | 0.00
****** | ***** | ALL | 100.00 | 0.00
:
:

検証

自動実行のしきい値（vacuum_sort_benefit）が上がる仕組みは？

早速、99.99%ソートされていないテーブルを作成して、検証に着手します。

cmdb=# CREATE TABLE IF NOT EXISTS "public"."lineorder_av"
(
"lo_orderkey" INTEGER ENCODE raw
,"lo_linenumber" INTEGER ENCODE lzo
,"lo_custkey" INTEGER ENCODE lzo
,"lo_partkey" INTEGER ENCODE lzo
,"lo_suppkey" INTEGER ENCODE lzo
,"lo_orderdate" INTEGER ENCODE lzo
,"lo_orderpriority" VARCHAR(15) ENCODE lzo
,"lo_shippriority" VARCHAR(1) ENCODE lzo
,"lo_quantity" INTEGER ENCODE lzo
,"lo_extendedprice" INTEGER ENCODE lzo
,"lo_ordertotalprice" INTEGER ENCODE lzo
,"lo_discount" INTEGER ENCODE lzo
,"lo_revenue" INTEGER ENCODE lzo
,"lo_supplycost" INTEGER ENCODE lzo
,"lo_tax" INTEGER ENCODE lzo
,"lo_commitdate" INTEGER ENCODE lzo
,"lo_shipmode" VARCHAR(10) ENCODE lzo
)
DISTSTYLE EVEN
SORTKEY(lo_orderkey)
;
CREATE TABLE

cmdb=# INSERT INTO lineorder_av SELECT * FROM sady_lineorder limit 1;
INSERT 0 1
cmdb=# INSERT INTO lineorder_av SELECT * FROM sady_lineorder limit 100000000;
INSERT 0 100000000

cmdb-# WHERE "schema" = 'public' AND "table" = 'lineorder_av';
schema | table | diststyle | unsorted | vacuum_sort_benefit
--------+--------------+-----------+----------+---------------------
public | lineorder_av | EVEN | 99.99 | 0.00
(1 row)

上記の通り、vacuum_sort_benefitは、0.00なのでAuto Vacuum Sortは自動実行しません。「スキャンクエリを追跡」とあるので、クエリを実行して確認します。

cmdb=# select count(*) from lineorder_av where lo_orderkey >= 515703591 and lo_orderkey <= 515703591; 
count 
------- 
7 (1 row) 

cmdb=# SELECT "schema", "table", diststyle, unsorted, vacuum_sort_benefit 
cmdb-# FROM SVV_TABLE_INFO 
cmdb-# WHERE "schema" = 'public' AND "table" = 'lineorder_av';
 schema | table | diststyle | unsorted | vacuum_sort_benefit 
--------+--------------+-----------+----------+--------------------- 
 public | lineorder_av | EVEN | 99.99 | 0.00 
(1 row)

スキャンクエリを実行しても、vacuum_sort_benefitは、0.00のまま変化がありませんでした。やっぱり困ったままだ、、、

データブロックの状態、ゾーンマップの状態を確認

次に「ソートするメリットのあるテーブルのセクションを判断」という記述があります。つまり、ソートされていないけどソートしなくても良いと判断した結果、0.00のままであると考えられます。そこで、データブロックの状態、ゾーンマップがどのような状態であるかを確認します。

cmdb=# SELECT svv_table_info."table", stv_blocklist.blocknum, stv_blocklist.minvalue, stv_blocklist.maxvalue FROM stv_blocklist 
cmdb-# INNER JOIN svv_table_info ON svv_table_info.table_id = stv_blocklist.tbl 
cmdb-# WHERE stv_blocklist.col = 0 AND stv_blocklist.slice = 0 AND svv_table_info."table" = 'lineorder_av' 
cmdb-# ORDER BY stv_blocklist.blocknum;

 table | blocknum | minvalue | maxvalue 
--------------+----------+-----------+----------- 
 lineorder_av | 0 | 6 | 6077222 
 lineorder_av | 1 | 6077248 | 12173063 
 lineorder_av | 2 | 12173063 | 18246721 
 lineorder_av | 3 | 18246721 | 24287685 
 lineorder_av | 4 | 24287685 | 30338818 
 lineorder_av | 5 | 30338883 | 36392583 
 lineorder_av | 6 | 36392647 | 42489955 
 lineorder_av | 7 | 42489988 | 48547847 
 lineorder_av | 8 | 48547971 | 54594272 
 lineorder_av | 9 | 54594273 | 60675492 
 lineorder_av | 10 | 60675558 | 66713991
  :
  :

ソートキーに指定しているlo_orderkeyのデータブロックごとの最大値と最小値を確認してみると、データブロックがソートされていることが確認できます。つまり、未ソートリージョンのデータブロックだけど、実態のデータブロックはソートされている状態ということです。データブロックはソートされているので、パフォーマンスの問題はない、つまりvacuum_sort_benefitは0.00のままで良いという仮説が立てられます。

意図的にソートされていないテーブルを作成して仮説検証する

では、その仮設が正しいことを検証するため、意図的にソートされていないテーブルを作成して検証します。

cmdb=# CREATE TABLE IF NOT EXISTS "public"."lineorder_unsorted" 
cmdb-# ( 
cmdb(# "lo_orderkey" INTEGER ENCODE raw 
cmdb(# ,"lo_linenumber" INTEGER ENCODE lzo 
cmdb(# ,"lo_custkey" INTEGER ENCODE lzo 
cmdb(# ,"lo_partkey" INTEGER ENCODE lzo 
cmdb(# ,"lo_suppkey" INTEGER ENCODE lzo 
cmdb(# ,"lo_orderdate" INTEGER ENCODE lzo 
cmdb(# ,"lo_orderpriority" VARCHAR(15) ENCODE lzo 
cmdb(# ,"lo_shippriority" VARCHAR(1) ENCODE lzo 
cmdb(# ,"lo_quantity" INTEGER ENCODE lzo 
cmdb(# ,"lo_extendedprice" INTEGER ENCODE lzo 
cmdb(# ,"lo_ordertotalprice" INTEGER ENCODE lzo 
cmdb(# ,"lo_discount" INTEGER ENCODE lzo 
cmdb(# ,"lo_revenue" INTEGER ENCODE lzo 
cmdb(# ,"lo_supplycost" INTEGER ENCODE lzo 
cmdb(# ,"lo_tax" INTEGER ENCODE lzo 
cmdb(# ,"lo_commitdate" INTEGER ENCODE lzo 
cmdb(# ,"lo_shipmode" VARCHAR(10) ENCODE lzo 
cmdb(# ) 
cmdb-# DISTSTYLE EVEN 
cmdb-# SORTKEY(lo_orderkey) 
cmdb-# ;
 CREATE TABLE 

cmdb=# INSERT INTO lineorder_unsorted SELECT * FROM sady_lineorder where lo_ordertotalprice%10 = 9 limit 10000000; 
INSERT 0 10000000 
cmdb=# INSERT INTO lineorder_unsorted SELECT * FROM sady_lineorder where lo_ordertotalprice%10 = 8 limit 10000000; 
INSERT 0 10000000 
cmdb=# INSERT INTO lineorder_unsorted SELECT * FROM sady_lineorder where lo_ordertotalprice%10 = 7 limit 10000000; 
INSERT 0 10000000 
cmdb=# INSERT INTO lineorder_unsorted SELECT * FROM sady_lineorder where lo_ordertotalprice%10 = 6 limit 10000000; 
INSERT 0 10000000 
cmdb=# INSERT INTO lineorder_unsorted SELECT * FROM sady_lineorder where lo_ordertotalprice%10 = 5 limit 10000000; 
INSERT 0 10000000 
cmdb=# INSERT INTO lineorder_unsorted SELECT * FROM sady_lineorder where lo_ordertotalprice%10 = 4 limit 10000000; 
INSERT 0 10000000 
cmdb=# INSERT INTO lineorder_unsorted SELECT * FROM sady_lineorder where lo_ordertotalprice%10 = 3 limit 10000000; 
INSERT 0 10000000 
cmdb=# INSERT INTO lineorder_unsorted SELECT * FROM sady_lineorder where lo_ordertotalprice%10 = 2 limit 10000000; 
INSERT 0 10000000 
cmdb=# INSERT INTO lineorder_unsorted SELECT * FROM sady_lineorder where lo_ordertotalprice%10 = 1 limit 10000000; 
INSERT 0 10000000 
cmdb=# INSERT INTO lineorder_unsorted SELECT * FROM sady_lineorder where lo_ordertotalprice%10 = 0 limit 10000000; 
INSERT 0 10000000 

cmdb=# SELECT "schema", "table", diststyle, unsorted, vacuum_sort_benefit 
cmdb-# FROM SVV_TABLE_INFO 
cmdb-# WHERE "schema" = 'public' AND "table" = 'lineorder_unsorted'; 
 schema | table | diststyle | unsorted | vacuum_sort_benefit 
--------+--------------------+-----------+----------+--------------------- 
 public | lineorder_unsorted | EVEN | 90.00 | 0.00 (1 row) 

cmdb=# SELECT svv_table_info."table", stv_blocklist.blocknum, stv_blocklist.minvalue, stv_blocklist.maxvalue FROM stv_blocklist 
cmdb-# INNER JOIN svv_table_info ON svv_table_info.table_id = stv_blocklist.tbl 
cmdb-# WHERE stv_blocklist.col = 0 AND stv_blocklist.slice = 0 AND svv_table_info."table" = 'lineorder_unsorted' 
cmdb-# ORDER BY stv_blocklist.blocknum; 
 table | blocknum | minvalue | maxvalue 
--------------------+----------+-----------+----------- 
 lineorder_unsorted | 0 | 37 | 60791525 
 lineorder_unsorted | 1 | 60791655 | 121892966 
 lineorder_unsorted | 2 | 121892966 | 183010177 
 lineorder_unsorted | 3 | 183010402 | 244010181 
 lineorder_unsorted | 4 | 244010374 | 305249060 
 lineorder_unsorted | 5 | 305249060 | 366150373 
 lineorder_unsorted | 6 | 366150438 | 427032580 
 lineorder_unsorted | 7 | 427032580 | 488053476 
 lineorder_unsorted | 8 | 488053863 | 548950279 
 lineorder_unsorted | 9 | 548950371 | 599999905 
 lineorder_unsorted | 10 | 130 | 61200256 
 lineorder_unsorted | 11 | 61200449 | 122244613 
 lineorder_unsorted | 12 | 122244737 | 183110182 
 lineorder_unsorted | 13 | 183110182 | 243937126
  : 
  :

90%のデータが未ソートの状態であり、実態のデータブロックはソートされていない状態であるとことが確認できます。引き続き、「スキャンクエリを追跡」とあるので、クエリを実行して確認します。

cmdb=# select count(*) from lineorder_unsorted where lo_orderkey >= 515703591 and lo_orderkey <= 515703591;
 count
-------
 7
(1 row)

cmdb=# SELECT "schema", "table", diststyle, unsorted, vacuum_sort_benefit
cmdb-# FROM SVV_TABLE_INFO
cmdb-# WHERE "schema" = 'public' AND "table" = 'lineorder_unsorted';
 schema | table | diststyle | unsorted | vacuum_sort_benefit
--------+--------------------+-----------+----------+---------------------
 public | lineorder_unsorted | EVEN | 90.00 | 10.00
(1 row)

想定通り、vacuum_sort_benefitが10.00になりました！！

lineorder_unsortedテーブルは、90%が物理的にソートされていなくても、90%ソートされていないテーブルからのクエリ実行の影響は、10%だけとなります。

まとめ

今回解説したとおり、ソートキー（コンパウンドソートキー）は、スキャンクエリを追跡し、ソートするメリットのあるテーブルのセクションを判断し、システムの負荷に応じて、自動でソートを開始します。仮にunsortedが100%だったとしても、データブロックがソート済みの場合はソートが不要なので、vacuum_sort_benefitが0.00となり、Auto Vacuum Sortは自動実行しません。vacuum_sort_benefitはスキャンクエリを実行した時にスキャン範囲の実績に基づきvacuum_sort_benefitが更新されるため、一度もスキャンクエリを実行していないテーブルもvacuum_sort_benefitが0.00になります。そのため、vacuum_sort_benefitが0.00だから常に最適なパフォーマンスが得られるとは限りませんが、これは不要なVacuumを避ける仕組みでもあります。これが許容できない場合は従来通りVacuumを手動実行すると良いでしょう。

また、Upsertを契機に累積した未ソートリージョン全てがVacuum Sortにならないか追加検証を実施しましたが、この場合、ソートキーに重なり合った部分のみがソートの対象となり、全てがソートの対象になることがありませんでした。本当に素晴らしい仕様だと思います！

基本的にAuto Vacuum SortとAuto Vacuum DeleteにVacuum動作を任せ、例外的に手動でVacuumしたいテーブルのみ、従来通り定期的に実行することになります。今後は、安心してAuto Vacuum SortとAuto Vacuum DeleteにVacuum動作を任せていただいて構いません。